﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.IO;
using FluentETL;
using FluentETL.Impl;

namespace FluentETL_Examples
{
    public static class Example_TextToDB
    {
        public static void Run()
        {
            string dataDir = ConfigurationManager.AppSettings["DataDirectory"];
            string inputFile = Path.Combine(dataDir, "employees.txt");
            string outputDB = Path.GetFullPath(Path.Combine(dataDir, "MyCompany.mdf"));
            string outputConnStr = @"Server=.\SQLExpress;AttachDbFilename=" + outputDB + ";Database=MyCompanyDB;Trusted_Connection=Yes;";

            ISource src = new TextSource()
                                        .From(inputFile)
                                        .SkipWhen(line => line.StartsWith("#")) // Skip comment lines
                                        .ParseAsDelimited(',', true);
            IDestination dest = new SqlDestination()
                                        .Connection(outputConnStr)
                                        .ToTable("Employees")
                                        .DeleteFirst();

            Transform t = new Transform(src, dest)
                                    .AutoMap()
                                    .OnStart(trans =>
                                    {
                                        Console.WriteLine("Starting transform...");
                                    })
                                    .Validate((row, rowValidation) =>
                                    {
                                        if (string.IsNullOrEmpty((string)row["Joined"]))
                                            rowValidation.Errors.Add("no_joined", "No joined date");
                                    })
                                    .OnRowInvalid((row, rowValidation) =>
                                    {
                                        Console.WriteLine("Invalid row [" + row["LastName"] + ", " + row["FirstName"] + "]");
                                        Console.WriteLine("\tErrors:");
                                        foreach (string err in rowValidation.Errors.Values)
                                            Console.WriteLine("\t\t" + err);
                                    })
                                    .OnRowAfterProcess((row, rowOp) =>
                                    {
                                        Console.WriteLine("Loaded employee [" + row["LastName"] + ", " + row["FirstName"] + "]");
                                    })
                                    .OnComplete(trans =>
                                    {
                                        Console.WriteLine("Transform complete!");
                                    })
                                    .Execute();
        }
    }
}
